L17: Valuation data processing

Valuation overview

We will learn how to value a firm (MSFT in our examples) using three different methods:

  1. Multiples
  2. Dividend Discount Model (DDM)
  3. Discounted Cash Flow (DCF) analysis

Before we start, we need to gather some data (instructions on how to get it below). All the files we download will go into a “Data” folder that you should create in the same folder where you saved these lecture notes.

Downloading financial statements

We will download financial statements from

https://www.morningstar.com/

For this purpose, you must create a free account with Morningstar first. Then:

  • Input the ticker of your firm in “Search Quotes and Site” at the top-left of the page.
  • Click “Financials”
  • Click “Expand Detail View”
  • Click “Export Data” (towards the right of the screen). This will download the Income Statement.
  • To download the balance sheet, select “Balance Sheet” and then hit “Export Data”.
  • Follow the same process for the Cash Flow statement.

We will create a “Valuation” excel file, and store each of these financial statements into a separate sheet in that file.

Industry-level data

These data come from Aswath Damodaran at NYU. If you want to download these data yourself, go to

http://pages.stern.nyu.edu/~adamodar/

Then, get the following files:

  • For “multiples” valuation, we will use data from the following files
    • The file that starts with “PE Ratios, …” (once downloaded, it will be called “pedata”)
    • The file that starts with “Price and Enterprise Value to Book Ratios …” (once downloaded, it will be called “pbdata”)
    • The file that starts with “Enterprise Value/EBIT …” (once downloaded, it will be called “vebitda”)
  • For DDM valuation, we will use data from the following files:
    • The file that starts with “Historical growth rate in EPS, …” (once downloaded, it will be called “histgr”)
    • The file that starts with “Fundamental growth rate in EPS, …” (once downloaded, it will be called “fundgr”)
  • For DCF valuation, we will use data from:
    • The “Cost of capital by industry” file (once downloaded, it will be called “wacc”)

Competitor data

To find a firm’s competitors (according to Morningstar), go to the firm’s Morningstar page (as explained above) and hit the “Quote” tab. Then hit “Competitors” (towards the middle of the page).

For MSFT, this should show GOOG (Alphabet), ORCL (Oracle), and CRM (Salesforce) as the competitors.

Hit “More Competitors Data” and you will see their Price/Earnings (P/E) and Price/Book (P/B) ratios.

To obtain more ratios (in particular, we will need Enterprise Value / EBITDA), we need to go to each competitor’s Morningstar page and hit the “Valuation” tab.

We will store each of these competitor ratios into a “Comps” sheet in the “Valuation” file we created above.

Returns data

We will need to estimate MSTF’s cost of equity. For this purpose, we need data on MSFT returns, and the returns to the Fama-French factors. We have gathered data like this several times in class, so I will not make you do it again. The “Data” subforder found together with these lecture notes on D2L contains a file named “msft_returns” that has all the data you need to estimate MSFTs cost of equity.